import sys
import System
import System.Threading
import clr
clr.AddReference('WindowsBase')
clr.AddReference('System.Windows.Presentation')
from System import Action
from System.Windows.Threading import DispatcherExtensions, Dispatcher

clr.AddReferenceByPartialName('MySql.Data')
from MySql.Data.MySqlClient import * 
from System.Collections import *
clr.AddReference('System.Data')
from System.Data.SqlClient import SqlConnection
clr.AddReference ("SAP_dll")
from SAP_dll import *
clr.AddReference ("Obvestila")
from Obvestila import *


conStrNtpe1 = 'server=ntpe1; userid=purkat; password=purkat; database=sledljivost;'
dbNtpe1 = MySqlConnection(conStrNtpe1) 

conStrPris = 'Data Source=vmie3;initial catalog=KAKOVOST;user id=uporabnik;password=uporabnik'
dbPris = SqlConnection(conStrPris)

serijskast = 'Z[0-9]{8}[G|H]'
procesnast = 'P[0-9]{8}[A|B]'


# Vrne regularni izraz ki se bo iskal
def Patterns(l):
    l.Add(serijskast)
    l.Add(procesnast)
    return l

#
# POVEZAVA Z NTPE1
#
def poizvedbaNTPE(stavek, izhod):
    l = ArrayList()
    while dbNtpe1.State.ToString() != System.Data.ConnectionState.Open.ToString():  
        try:
          dbNtpe1.Open()
          cmd = dbNtpe1.CreateCommand() 
          cmd.CommandText = stavek
          r = cmd.ExecuteReader () 
          l = ArrayList()
          while r.Read (): 
              vrednost = izhod
              for x in range(r.FieldCount):
                  vrednost = vrednost.replace("{%s}" %r.GetName(x) ,r[x].ToString())
              l.Add(vrednost)
          r.Close () 
        except Exception, e:
          print ("Povezava na NTPE1 neuspesna!"),
          System.Threading.Thread.Sleep(2000)
          sys.stderr.write("Stavek:%s\n\r %s" %(stavek, e.message.ToString()))
    dbNtpe1.Close () 
    return l

#
# POVEZAVA Z PRIS
#
def poizvedbaPRIS(stavek, izhod):
    l = ArrayList()
    while dbPris.State.ToString() != System.Data.ConnectionState.Open.ToString():
      try:
          dbPris.Open()
          cmd = dbPris.CreateCommand() 
          cmd.CommandText = stavek
          r = cmd.ExecuteReader () 
          l = ArrayList()
          while r.Read (): 
              vrednost = izhod
              for x in range(r.FieldCount):
                  vrednost = vrednost.replace("{%s}" %r.GetName(x) ,r[x].ToString())
              l.Add(vrednost)
          r.Close () 
      except Exception, e:
          print ("Povezava na PRIS neuspesna!"),
          System.Threading.Thread.Sleep(2000)
          sys.stderr.write("%s" %e.message.ToString())
    dbPris.Close () 
    return l

##############################################################

##############################################################


# Vrne ArrayList izdelkov
def izdelki():
    print "Izdelki dodani"
    return poizvedbaNTPE("SELECT DISTINCT `Program` FROM `sledljivost`.`asysaba04` ORDER BY `Program`;", "{Program}")


# Vrne ArrayList izdelkov
def delovninalogi(izdelek):
    print "Delovni nalogi dodani"
    return poizvedbaNTPE("SELECT DISTINCT `DN` FROM `sledljivost`.`asysaba04` WHERE `Program` = '%s'" %izdelek, "{DN}")

# Vrne OPIS in KRATICO operacije
def operacije():
    print "Operacije dodani"
    return poizvedbaPRIS("SELECT [ATE], [ZNAK] FROM [KAKOVOST].[dbo].[SEZATE] ORDER BY [ID];", "{ZNAK}\t{ATE}")

# Vrne OPERATERJA, NJEGOVO STEVILKO in DELAVSKO STEVILKO
def operaterji():
    print "Operaterji dodani"
    return poizvedbaPRIS("SELECT [ID], [KONTROLOR], [IME], [DELSTEV] FROM [KAKOVOST].[dbo].[SEZKONTROLORJEV] WHERE [STATUS] = 0 ORDER BY [IME];", "{ID}\t{KONTROLOR}\t{IME}")

# Vrne WBS za izbrani DN
def wbs(dn):
    print "WBS dodan"
    return poizvedbaPRIS("SELECT [WBS] FROM [KAKOVOST].[dbo].[DELNALOGI] WHERE [DELNALOG] = '%s';" %dn, "{WBS}")

# Vrne IZDELEK, DEL NALOG,  za izbrani DN
def procesna(pno):
    if (len(pno) != 10):
        print "Dolzina ni prava"
        return None
    l = ArrayList()
    while dbNtpe1.State.ToString() != System.Data.ConnectionState.Open.ToString():
      try:
          dbNtpe1.Open()
          cmd = dbNtpe1.CreateCommand() 
          cmd.CommandText = "SELECT `DN`, `Program` FROM `sledljivost`.`asysaba04` WHERE `Procesna` = '%s'" %pno
          r = cmd.ExecuteReader () 
          l = ArrayList()
          while r.Read (): 
            l.Add(r['DN'])
            l.Add(r['Program'])
          r.Close () 
      except Exception, e:
          print "Povezava na NTPE1 neuspesna!"
          sys.stderr.write("%s" %e.message.ToString())
    dbNtpe1.Close () 
    print "Nasel: %s %s" %(l[0], l[1])
    return l

# Ko keyhooker pove svoje
def Catch(pattern, value):
  while (1 == 1):
    try:
        if (pattern is serijskast):
            procesna_ = SAP400().getProcess(value)
            serijska_ = value
            print "SERIJSKA: %s PROCESNA: %s" %(serijska_, procesna_)
        elif (pattern is procesnast):
            procesna_ = value
            serijska_ = SAP400().getSerial(value)
            print "SERIJSKA: %s PROCESNA: %s" %(serijska_, procesna_)
        else:
            print "NEMOGOCE"
        Class1().getSerialXX("%s" %procesna_)
        return procesna_
    except Exception, e:
        print ("Poizvedba SAP ni uspela!"),
        sys.stderr.write("%s" %e.message.ToString())

